import pymysql
import logging
import subprocess
import re
import datetime

import myLogger


def catchWrongSql(func):
    """
    包裹每一个SQL函数, 用来捕获并记录失败的sql语句
    """
    def wrapper(self, *args, **kwargs):
        try:
            ret = func(self, *args, **kwargs)
        except pymysql.Error as e:
            print("catch")
            if self.logger is not None:
                self.logger.error(f"wrong sql: {self._sql}")
            raise e
        return ret

    return wrapper


def getMysqlStatus():
    """
    在windows凭条获取mysqld服务状态
    """
    cmd = "sc query mysqld"
    result = subprocess.run(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=True)
    output = result.stdout.decode("utf-8")
    match = re.search("STATE.*RUNNING", output)
    if match:
        return True
    else:
        return False


class MysqlConnector:
    _sql: str  # 保存上一次执行的sql语句, 用来知道上一次sql出错在哪

    def __init__(self, host, user, password, database, logger: logging.Logger = None) -> None:
        """
        启动并初始化mysql连接
        """
        # 打开数据库连接
        self.db = pymysql.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )
        # 创建一个游标对象
        self.cursor = self.db.cursor()
        self.logger = logger
        if self.logger is not None:
            self.logger.info(f"MySQL connection start at {datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")

    def CloseMysql(self):
        """
        关闭mysql连接
        """
        self.db.close()
        if self.logger is not None:
            self.logger.info(f"MySQL connection close at {datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")

    @catchWrongSql
    def Execute(self, sql) -> int:
        """
        做爱做的事, 没有回显, 有log
        :return 影响的行数
        """
        self._sql = sql
        self.cursor.execute(sql)
        self.db.commit()
        if self.logger is not None:
            self.logger.info(f"Execute {self.cursor.rowcount} rows: {sql}")
        return self.cursor.rowcount

    @catchWrongSql
    def Select(self, sql) -> tuple:
        """
        select查询, 有内容会返回二维元组, 例如((1,),)
        没有内容会返回()
        """
        self._sql = sql
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        if self.logger is not None:
            self.logger.info(f"Select {self.cursor.rowcount} rows: {sql}")
        return data

    @catchWrongSql
    def Insert(self, tableName: str, content: dict) -> int:
        """
        解析字典, 插入表, 有log
        TODO: 突然发现这个函数似乎只能插入一条数据
        :return 影响的行数
        """
        column = str(tuple(content.keys())).replace("'", "`")
        values = str(tuple(content.values())).replace("''", "NULL")
        sql = f'insert into {tableName} {column} values {values}'
        self._sql = sql
        self.cursor.execute(sql)
        self.db.commit()
        if self.logger is not None:
            self.logger.info(f"Insert {self.cursor.rowcount} rows: {sql}")
        return self.cursor.rowcount


if __name__ == '__main__':
    import config

    sqlConn = MysqlConnector("127.0.0.1", "CsCN", "123456", "cscn", myLogger.initLogger("log/sql.txt", __name__))
    # dt = datetime.datetime.fromtimestamp(int(time.time()) + 60 * 30 * 48).strftime('%Y-%m-%d %H:%M:%S')
    # con = {"session_id": 111, "uid": 222, "ip": "127.0.0.1", "expire_time": dt}
    # con1 = {"session_id": 333, "uid": 444, "ip": "127.0.0.1", "expire_time": dt}
    # print(sqlConn.Insert(config.SESSION_TABLE, con))
    # print(sqlConn.Insert(config.SESSION_TABLE, con1))
    # print(sqlConn.Select("select * from sessions"))
    # sqlConn.CloseMysql()
    #     import config
